package db_utils

import (
	"dgo/goutils/utils"
	"fmt"
	"strings"
)

type SQLBuilder struct {
	sb         strings.Builder
	groupLevel int
	Whereflag  int
}

func NewSQLBuilder() *SQLBuilder {
	rval := &SQLBuilder{}
	return rval
}

func (this *SQLBuilder) Select() *SQLBuilder {
	this.sb.WriteString("SELECT ")
	return this
}

func (this *SQLBuilder) SelectDistinctFields(fields ...string) *SQLBuilder {
	this.sb.WriteString("SELECT DISTINCT ")
	this.Fields(fields...)
	return this
}

func (this *SQLBuilder) SelectFields(fields ...string) *SQLBuilder {
	this.sb.WriteString("SELECT ")
	this.Fields(fields...)
	return this
}

func (this *SQLBuilder) Comma() *SQLBuilder {
	this.sb.WriteString(", ")
	return this
}

func (this *SQLBuilder) SelecFieldsPrefix(prefix string, fields ...string) *SQLBuilder {
	this.sb.WriteString("SELECT ")
	this.FieldsPrefix(prefix, fields...)
	return this
}

func (this *SQLBuilder) FieldsPrefix(prefix string, fields ...string) *SQLBuilder {
	j := 0
	for _, v := range fields {
		str := utils.Trim(v)
		if len(str) > 0 {
			strs := strings.Split(str, ",")
			for _, v1 := range strs {
				fd := utils.Trim(v1)
				if len(fd) > 0 {
					if j > 0 {
						this.sb.WriteString(", ")
					}
					if len(prefix) > 0 {
						this.sb.WriteString(prefix)
					}
					this.sb.WriteString(fd)
					j++
				}
			}
		}
	}
	return this
}

func (this *SQLBuilder) Fields(fields ...string) *SQLBuilder {
	for k, v := range fields {
		if k > 0 {
			this.sb.WriteString(",")
		}
		this.sb.WriteString(v)
	}
	return this
}

func (this *SQLBuilder) From(table string) *SQLBuilder {
	this.sb.WriteString(" FROM ")
	this.sb.WriteString(table)
	return this
}

func (this *SQLBuilder) Where() *SQLBuilder {
	this.sb.WriteString(" WHERE ")
	this.Whereflag = 1
	return this
}

/*
*
如果存在Where 执行fn, 不存在执行Where
*/
func (this *SQLBuilder) CheckWhere(fn func()) *SQLBuilder {
	if this.Whereflag != 1 {
		this.Where()
	} else {
		if fn != nil {
			fn()
		}
	}
	return this
}

/*
*
如果存在Where 执行And(), 不存在执行Where
*/
func (this *SQLBuilder) CheckWhereAnd() *SQLBuilder {
	if this.Whereflag != 1 {
		this.Where()
	} else {
		this.And()
	}
	return this
}

func (this *SQLBuilder) CheckAnd() *SQLBuilder {
	if this.Len() > 0 {
		this.And()
	}
	return this
}

func (this *SQLBuilder) GBegin() *SQLBuilder {
	this.sb.WriteString("(")
	this.groupLevel++
	return this
}

func (this *SQLBuilder) GEnd() *SQLBuilder {
	this.sb.WriteString(")")
	this.groupLevel--
	return this
}

func (this *SQLBuilder) And() *SQLBuilder {
	this.sb.WriteString(" AND ")
	return this
}

func (this *SQLBuilder) AndEqual(keyFieldName string, val interface{}) *SQLBuilder {
	this.sb.WriteString(" AND ")
	return this.Equal(keyFieldName, val)
}

func (this *SQLBuilder) Or() *SQLBuilder {
	this.sb.WriteString(" OR ")
	return this
}

func (this *SQLBuilder) EqualAny() *SQLBuilder {
	this.sb.WriteString(" 1=1 ")
	return this
}

func (this *SQLBuilder) Delete(table string) *SQLBuilder {
	this.sb.WriteString("DELETE FROM ")
	this.sb.WriteString(table)
	this.sb.WriteString(" ")
	return this
}

func (this *SQLBuilder) Equal(keyFieldName string, val interface{}) *SQLBuilder {
	if val == nil {
		this.sb.WriteString(fmt.Sprintf("%s IS NULL", keyFieldName))
	} else {
		strVal := ToSQLVal(val)
		this.sb.WriteString(fmt.Sprintf("%s=%s", keyFieldName, strVal))
	}

	return this
}

func (this *SQLBuilder) IsNullOrEmpty(keyFieldName string) *SQLBuilder {

	this.sb.WriteString(fmt.Sprintf("(%s IS NULL OR %s='')", keyFieldName, keyFieldName))

	return this
}

func (this *SQLBuilder) IsNull(keyFieldName string) *SQLBuilder {

	this.sb.WriteString(fmt.Sprintf("%s IS NULL", keyFieldName))
	return this
}

func (this *SQLBuilder) NotEqual(keyFieldName string, val interface{}) *SQLBuilder {
	if val == nil {
		this.sb.WriteString(fmt.Sprintf("%s IS NOT NULL", keyFieldName))
	} else {
		strVal := ToSQLVal(val)
		this.sb.WriteString(fmt.Sprintf("%s<>%s", keyFieldName, strVal))
	}

	return this
}

/***
 * 超过一个条件用AND 连接
 */
func (this *SQLBuilder) EqualValues(kvPairs ...interface{}) *SQLBuilder {
	this.Values(" AND ", "=", kvPairs...)
	return this
}

/*
**
  - "fid", "1,2,%NULL%,"
    "fid", "1,2,,%NULL%"
    fid = '1' or fid is null or fid =”
*/
func (this *SQLBuilder) EqualFieldMultiOrStr(field string, values string, sep string) *SQLBuilder {
	vals := strings.Split(values, sep)
	for k, v := range vals {
		if k > 0 {
			this.Or()
		}
		if v == "%NULL%" {
			this.EqualNull(field)
		} else {
			this.Equal(field, v)
		}

	}
	return this
}

func (this *SQLBuilder) EqualFieldMultiOr(field string, val ...interface{}) *SQLBuilder {
	for k, v := range val {
		if k > 0 {
			this.Or()
		}
		this.Equal(field, v)
	}
	return this
}

func (this *SQLBuilder) EqualFieldMultiOr_Strlist(field string, val ...string) *SQLBuilder {
	for k, v := range val {
		if k > 0 {
			this.Or()
		}
		this.Equal(field, v)
	}
	return this
}

func (this *SQLBuilder) EqualMultiOr(val interface{}, fields ...string) *SQLBuilder {
	strVal := ToSQLVal(val)
	for k, v := range fields {
		if k > 0 {
			this.sb.WriteString(" OR ")
		}
		this.sb.WriteString(fmt.Sprintf("%s=%s", v, strVal))
	}
	return this
}

func (this *SQLBuilder) LikeOr(val interface{}, fields ...string) *SQLBuilder {
	strVal := ToSQLVal(val)
	for k, v := range fields {
		if k > 0 {
			this.sb.WriteString(" OR ")
		}
		this.sb.WriteString(fmt.Sprintf("%s like %s", v, strVal))
	}
	return this
}

//func (this *SQLBuilder) LikeOrMultiFunc(fn func(field string) string, fields ...string) *SQLBuilder {
//	for k, v := range fields {
//		if k > 0 {
//			this.sb.WriteString(" OR ")
//		}
//		this.sb.WriteString(fmt.Sprintf("%s like '%s'", v, fn(k)))
//	}
//	return this
//}

func (this *SQLBuilder) BeginWith(field, startstr string) *SQLBuilder {
	this.sb.WriteString(field)
	this.sb.WriteString(" LIKE '")
	this.sb.WriteString(startstr)
	this.sb.WriteString("%'")
	return this
}

func (this *SQLBuilder) EqualNull(field string) *SQLBuilder {
	this.sb.WriteString(fmt.Sprintf("%s is null", field))
	return this
}

func (this *SQLBuilder) Condi(condi string) *SQLBuilder {
	this.sb.WriteString(condi)
	return this
}

func (this *SQLBuilder) LeftJoin(tablename string) *SQLBuilder {
	this.sb.WriteString(" LEFT JOIN ")
	this.sb.WriteString(tablename)
	return this
}

func (this *SQLBuilder) AddAny(any string) *SQLBuilder {
	this.sb.WriteString(any)
	return this
}

func (this *SQLBuilder) AddFmt(format string, arg ...interface{}) *SQLBuilder {
	this.sb.WriteString(fmt.Sprintf(format, arg...))
	return this
}

func (this *SQLBuilder) Not() *SQLBuilder {
	this.sb.WriteString(" NOT ")
	return this
}

func (this *SQLBuilder) Update(table string) *SQLBuilder {
	this.sb.WriteString("UPDATE ")
	this.sb.WriteString(table)
	this.sb.WriteString(" ")
	return this
}

func (this *SQLBuilder) SetFieldValues(kvPairs ...interface{}) *SQLBuilder {
	this.Values(",", "=", kvPairs...)
	return this
}

func (this *SQLBuilder) Set(kvPairs ...interface{}) *SQLBuilder {
	this.sb.WriteString(" SET ")
	this.Values(",", "=", kvPairs...)
	return this
}

func (this *SQLBuilder) WriteSqlValues(spliter, joinKv string, kvPairs ...string) *SQLBuilder {
	l := len(kvPairs)
	if l%2 != 0 { // 长度必须是双数
		panic("kv Pairs 长度必须是双数")
	}
	j := 0
	i := 0
	for i < l {
		key := kvPairs[i]
		if j > 0 {
			this.sb.WriteString(spliter)
		}
		this.sb.WriteString(key)
		this.sb.WriteString(joinKv)
		this.sb.WriteString(kvPairs[i+1])
		j++

		i += 2
	}

	return this
}

func (this *SQLBuilder) Values(spliter, joinKv string, kvPairs ...interface{}) *SQLBuilder {
	l := len(kvPairs)
	if l%2 != 0 { // 长度必须是双数
		panic("kv Pairs 长度必须是双数")
	}
	j := 0
	i := 0
	for i < l {
		if key, ok := kvPairs[i].(string); ok {
			if j > 0 {
				this.sb.WriteString(spliter)
			}
			this.sb.WriteString(key)
			this.sb.WriteString(joinKv)
			this.sb.WriteString(ToSQLVal(kvPairs[i+1]))
			j++
		}
		i += 2
	}

	return this
}

func (this *SQLBuilder) Len() int {
	return this.sb.Len()
}

func (this *SQLBuilder) OrderBy() *SQLBuilder {
	this.sb.WriteString(" ORDER BY ")
	return this
}

func (this *SQLBuilder) Ascend(field string) *SQLBuilder {
	this.sb.WriteString(" ")
	this.sb.WriteString(field)
	this.sb.WriteString(" ")
	return this
}

func (this *SQLBuilder) Desc(field string) *SQLBuilder {
	this.sb.WriteString(field)
	this.sb.WriteString(" DESC")
	return this
}

func (this *SQLBuilder) Limit(posi, num int) *SQLBuilder {
	this.sb.WriteString(fmt.Sprintf(" LIMIT %d, %d ", posi, num))
	return this
}

func (this *SQLBuilder) Contain(field, substr string) *SQLBuilder {
	this.sb.WriteString(field)
	this.sb.WriteString(" LIKE '%")
	this.sb.WriteString(substr)
	this.sb.WriteString("%'")
	return this
}

func (this *SQLBuilder) Gt(key string, val interface{}) *SQLBuilder {
	this.sb.WriteString(key)
	this.sb.WriteString(">")
	this.sb.WriteString(ToSQLVal(val))
	return this
}

/**
 * Greater than or Equal
 */
func (this *SQLBuilder) GtEq(key string, val interface{}) *SQLBuilder {
	this.sb.WriteString(key)
	this.sb.WriteString(">=")
	this.sb.WriteString(ToSQLVal(val))
	return this
}

/*
*

	Less than
*/
func (this *SQLBuilder) Lt(key string, val interface{}) *SQLBuilder {
	this.sb.WriteString(key)
	this.sb.WriteString("<")
	this.sb.WriteString(ToSQLVal(val))
	return this
}

/*
*
less than or equal
*/
func (this *SQLBuilder) LtEq(key string, val interface{}) *SQLBuilder {
	this.sb.WriteString(key)
	this.sb.WriteString("<=")
	this.sb.WriteString(ToSQLVal(val))
	return this
}

func (this *SQLBuilder) Between(key string, start interface{}, end interface{}) *SQLBuilder {
	this.sb.WriteString(key)
	this.sb.WriteString(" BETWEEN ")
	this.sb.WriteString(ToSQLVal(start))
	this.sb.WriteString(" AND ")
	this.sb.WriteString(ToSQLVal(end))
	return this
}

func (this *SQLBuilder) Reset(sql string) *SQLBuilder {
	this.sb.Reset()
	this.sb.WriteString(sql)
	if len(sql) == 0 {
		this.Whereflag = 0
	} else {
		if strings.Contains(strings.ToUpper(sql), "WHERE") {
			this.Whereflag = 1
		} else {
			this.Whereflag = 0
		}
	}
	return this
}

func (this *SQLBuilder) Add(sqlBuider *SQLBuilder) *SQLBuilder {
	this.sb.WriteString(sqlBuider.String())
	return this
}

func (this *SQLBuilder) String() string {
	return this.sb.String()
}
